﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using System.Configuration;
using System.ComponentModel.DataAnnotations;
using TracNghiem.Models;
using System.Collections.ObjectModel;
using System.Web.Mvc;
using System.Data.OracleClient;

namespace TracNghiem.Areas.Admin.Models
{
    public class Test_QuestionModel
    {
        public string id { get; set; }
        [Required(ErrorMessage = "Tên chủ đề không được để trống!")]
        public string name{ get; set; }        
        public string ghi_chu{ get; set; }
        public string status { get; set; }
        public string ngay_tao { get; set; }
        public string ngay_ket_thuc { get; set; }
        public string ten_phan_loai { get; set; }
        public string phanloai_id { get; set; }
        public SelectList option_id { get; set; }
        //Them field cho edit de thi pattern
        public string de_thi_id { get; set; }

        public List<Test_QuestionModel> getTest_Question(int id)
        {
            List<Test_QuestionModel> test_question = new List<Test_QuestionModel>();
            string sql = "select a.*,b.name ten_phan_loai,dichvu_id from cau_hoi a, phanloai b where a.phanloai_id=b.id and dichvu_id="+id +" order by a.id";             
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            int ngay_taoIndex=0;
            int ngay_ket_thucIndex=0;
            while (odr.Read())
            {
                ngay_taoIndex = odr.GetOrdinal("ngay_tao");
                ngay_ket_thucIndex = odr.GetOrdinal("ngay_ket_thuc");
                test_question.Add(new Test_QuestionModel
                {
                    id = odr["id"].ToString(),
                    name = odr["name"].ToString(),
                    ghi_chu = odr["ghi_chu"].ToString(),
                    status = odr["status"].ToString()=="1"?"Bình thường":"Khóa",
                    ngay_tao = odr.IsDBNull(ngay_taoIndex) ? null : odr.GetDateTime(ngay_taoIndex).ToString("dd/MM/yyyy"),
                    ngay_ket_thuc = odr.IsDBNull(ngay_ket_thucIndex) ? null : odr.GetDateTime(ngay_ket_thucIndex).ToString("dd/MM/yyyy"),
                    phanloai_id = odr["phanloai_id"].ToString(),
                    ten_phan_loai = odr["ten_phan_loai"].ToString()
                });
            }
            General.conn_global.Close();
            return test_question;
        }

        public List<Test_QuestionModel> getTest_QuestionForDethi_Pattern(int id)
        {
            List<Test_QuestionModel> test_question = new List<Test_QuestionModel>();
            string sql = "select c.de_thi_id,a.*,b.name ten_phan_loai,dichvu_id from cau_hoi a, phanloai b,(select de_thi_id,b.cau_hoi_id from de_thi_mau, table(cau_hoi_id) b) c where a.phanloai_id=b.id and dichvu_id=" + id + " and a.id=c.cau_hoi_id(+) order by a.id";
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            int ngay_taoIndex = 0;
            int ngay_ket_thucIndex = 0;
            while (odr.Read())
            {
                ngay_taoIndex = odr.GetOrdinal("ngay_tao");
                ngay_ket_thucIndex = odr.GetOrdinal("ngay_ket_thuc");
                test_question.Add(new Test_QuestionModel
                {
                    id = odr["id"].ToString(),
                    name = odr["name"].ToString(),
                    ghi_chu = odr["ghi_chu"].ToString(),
                    status = odr["status"].ToString() == "1" ? "Bình thường" : "Khóa",
                    ngay_tao = odr.IsDBNull(ngay_taoIndex) ? null : odr.GetDateTime(ngay_taoIndex).ToString("dd/MM/yyyy"),
                    ngay_ket_thuc = odr.IsDBNull(ngay_ket_thucIndex) ? null : odr.GetDateTime(ngay_ket_thucIndex).ToString("dd/MM/yyyy"),
                    phanloai_id = odr["phanloai_id"].ToString(),
                    de_thi_id = odr["de_thi_id"].ToString(),
                    ten_phan_loai = odr["ten_phan_loai"].ToString()
                });
            }
            General.conn_global.Close();
            return test_question;
        }

        public void addTest_Question(Test_QuestionModel ts)
        {
            string ngay_ket_thuc="";
            if(ts.ngay_ket_thuc!=null)
                ngay_ket_thuc = "to_date('" + ts.ngay_ket_thuc+"','dd/mm/yyyy'),";
            else
                ngay_ket_thuc = "to_date('31/1/2024','dd/mm/yyyy'),";
            string sql = "INSERT INTO cau_hoi";
            sql += " select nvl((select max(id)+1 from cau_hoi),1),trim('" + ts.name + "'),'" + ts.ghi_chu + "',"+ts.status+",to_date('" + ts.ngay_tao + "','dd/mm/yyyy'),"+ngay_ket_thuc+"'" + ts.phanloai_id + "' from dual";
            General.conn_global.Close();
            General.Lib_ExecuteNonQuery(sql);
        }
        public void addTest_Answer(string name,int status,int vi_tri)
        {
            string sql = "INSERT INTO cau_traloi";
            sql += " select nvl((select max(id)+1 from cau_traloi),1),trim('" + name + "')," + status + ",(select max(id) from cau_hoi),"+vi_tri+" from dual";
            General.Lib_ExecuteNonQuery(sql);
            General.conn_global.Close();
        }
        public void updateTest_Question(Test_QuestionModel ts)
        {
            string ngay_ket_thuc = "";
            if (ts.ngay_ket_thuc != null)
                ngay_ket_thuc = "ngay_ket_thuc=to_date('" + ts.ngay_ket_thuc + "','dd/mm/yyyy'),";
            else
                ngay_ket_thuc = "ngay_ket_thuc=to_date('31/1/2024','dd/mm/yyyy'),";
            string sql = "UPDATE cau_hoi SET name='" + ts.name + "', ghi_chu='" + ts.ghi_chu + "', status=" + ts.status + "," + ngay_ket_thuc + "phanloai_id=" + ts.phanloai_id + " where id='" + ts.id + "'";
            General.Lib_ExecuteNonQuery(sql);
            General.conn_global.Close();
        }
        public void deleteTest_Question(int id)
        {
            string sql = "DELETE FROM cau_hoi WHERE id=" + id + "";
            General.Lib_ExecuteNonQuery(sql);
            string sql2 = "DELETE FROM cau_traloi where cau_hoi_id="+id;
            General.Lib_ExecuteNonQuery(sql2);
            General.conn_global.Close();
        }
        public int checkExist(Test_QuestionModel ts)
        {
            string sql = "SELECT * FROM cau_hoi WHERE name='" + ts.name + "'";
            
            return General.Lib_CheckExist(sql);
        }
        public int checkSubItem(int id)
        {
            string sql = "SELECT c.cau_hoi_id FROM bai_thi a,table(a.ket_qua) b,cau_traloi c where c.id=b.cau_tl_id and c.cau_hoi_id='" + id + "'";
            sql += " union all select b.cau_hoi_id from de_thi_mau,table(cau_hoi_id) b where b.cau_hoi_id=" + id;
 
            return General.Lib_CheckExist(sql);
        }

        public List<Test_QuestionModel> FindTest_Question(int id,string str)
        {
            List<Test_QuestionModel> test_question = new List<Test_QuestionModel>();
            string sql = "select a.*,b.name ten_phan_loai,dichvu_id from cau_hoi a, phanloai b where a.phanloai_id=b.id and b.dichvu_id=" + id + " and " + str;
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            int ngay_taoIndex = 0;
            int ngay_ket_thucIndex = 0;
            while (odr.Read())
            {
                ngay_taoIndex = odr.GetOrdinal("ngay_tao");
                ngay_ket_thucIndex = odr.GetOrdinal("ngay_ket_thuc");
                test_question.Add(new Test_QuestionModel
                {
                    id = odr["id"].ToString(),
                    name = odr["name"].ToString(),
                    ghi_chu = odr["ghi_chu"].ToString(),
                    status = odr["status"].ToString() == "1" ? "Bình thường" : "Khóa",
                    ngay_tao = odr.IsDBNull(ngay_taoIndex) ? null : odr.GetDateTime(ngay_taoIndex).ToString("dd/MM/yyyy"),
                    ngay_ket_thuc = odr.IsDBNull(ngay_ket_thucIndex) ? null : odr.GetDateTime(ngay_ket_thucIndex).ToString("dd/MM/yyyy"),                    
                    ten_phan_loai = odr["ten_phan_loai"].ToString()
                });
            }
            General.conn_global.Close();
            return test_question;
        }
        
    }
}